# 爬虫 + 数据处理(一)
#
# < font
# color = 'red' > ** 【题目1】 ** < / font > B站上的python教学视频美不胜收，某python程序员拟对这些python视频进行归纳，需要种子页面的各python教学视频的标题、资源图的url和视频的url。已知种子url为：url = r'https://search.bilibili.com/all?keyword=python'
#
# 本题可能用到的库:
#
# import requests
#
# from bs4 import BeautifulSoup
#
# import re
#
# 要求：控制台输出种子url页面各 **①python教学视频的标题 **、 ** ②资源图的url ** 和 **③视频的url **。如下所示：
#
# ![](C:\Users\zhuoym\文心一言\images\B站Python视频教程.png)

import requests
import re
import csv

# 1. 拿到网页源码
url = r'https://search.bilibili.com/all?keyword=python'
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.93 Safari/537.36'
}
resp = requests.get(url, headers=headers)
page_content = resp.content.decode("utf-8")
# print(page_content)
with open("mybilibili.html", "w", encoding="utf-8") as f:
    f.write(page_content)

# 2. 通过re来提取想要的有效信息
# 解析数据
# videoTitle,imgURL,videoURL
pat_str = r'''<a href="(?P<videoURL>.*?)".*?'''  # videoURL
pat_str = pat_str + r'''<picture class=.*?><img src="(?P<imgURL>.*?)@.*?alt="(?P<videoTitle>.*?)".*?>.*?</picture>'''  # videoTitle imgURL
pattern = re.compile(pat_str, re.S)
result = pattern.finditer(page_content)
# print(result)

# 3. 保存数据
with open("data_bilibili.csv", mode='w', encoding='utf-8') as f:
    csvwriter = csv.writer(f)
    num = 1  # 计数器
    for it in result:
        # groupdict()是re.MatchObject对象的另一个方法，
        # 它返回一个字典，其中健为命名组的名称，值为相应组的匹配字符串。
        # 这意味着，如果你想使用命名捕获分组，
        # 则可以使用groupdict()来轻松获取每个组的值。
        dic = it.groupdict()
        # csvwriter.writerow(dic.values()])
        row = [num, dic["videoTitle"], "https:" + dic["imgURL"], "https:" + dic["videoURL"]]
        csvwriter.writerow(row)
        num += 1
print('over!')

# * * *
#
# < font
# color = 'red' > ** 【题目2】 ** < / font > 在上题基础上，将种子页面上各视频的 **①标题 **、对应的 **②展示图片url ** 和 **③视频url ** 持久化到本地mysql数据库中。可以使用方法一或其它方法，结果一致即可。
#
# ** 方法一： **
#
# *(1)
# 创建MySQL连接与游标；
#
# *(2)
# 创建数据表
# `pythonVideo`，包含
# `(id, videoTitle, imgURL, videoURL)`，即 ** 编号 **、 ** 本视频的标题 **、 ** 本视频导图的网址 **、 ** 本视频的网址 ** 等四个字段
#
# *(3)
# 在数据表
# `pythonVideo`
# 中插入上题中爬取的数据
#
# *(4)
# 查询并输出数据表
# `pythonVideo`
# 的全部记录
#
# *(5)
# 关闭游标和MySQL连接
#
# # 说明：数据库finaltest及表pythonVideo可以不在程序中创建，而是事先以命令行的方式创建好

import csv
from pymysql import connect

# 读取'./data_bilibili.csv'，并将内容保存到数据库
with open('./data_bilibili.csv', mode='r', encoding='utf-8') as f:
    reader = csv.reader(f, delimiter=',')

    # 1. 创建Connection连接
    conn = connect(host='localhost', port=3306, user='root', password='12345678', database='finaltest', charset='utf8')
    # 2. 获得Cursor对象
    cs = conn.cursor()

    for row in reader:
        tem_id = int(row[0])
        tem_videoTitle = row[1]
        tem_imgURL = row[2]
        tem_videoURL = row[3]

        try:
            sql = "insert into pythonVideo(id, videoTitle, imgURL, videoURL) values ('%d', '%s', '%s', '%s');" % (
            tem_id, tem_videoTitle, tem_imgURL, tem_videoURL)
            row_count = cs.execute(sql)  # row_count:sql语句执行影响的行数
            conn.commit()
        except Exception as e:
            # 回滚数据
            print("有问题！！！")
            conn.rollback()

    # 查询并输出数据表`pythonVideo`的全部记录
    sql = "select * from pythonVideo;"
    row_count = cs.execute(sql)
    print(f"SQL 语句执行影响的行数{row_count}")
    # 取出结果集中的所有数据
    for line in cs.fetchall():
        print(line)

    cs.close()
    conn.close()